package com.shxt.hotel.background;

import com.shxt.hotel.util.*;
import com.shxt.hotel.conf.*;
import java.util.Map;
import java.util.HashMap;
import java.util.Calendar;
import java.util.List;

public class Room{
    
    private static final String TABLE_NAME = "room";
    private static final String CATEGORY_TABLE_NAME = "room_category";
    private static final String SUB_TABLE_NAME = "sub_info";
    
    DBManager dbm;
    
    public Room()
    throws Exception{
        try{
            dbm = new DBManager(Config.DB_NAME, Config.DB_USERNAME, Config.DB_PASSWORD);
        }catch(Exception e){
            throw e;
        }
    }
    
    public List<Map<String, Object>> getCategories(String selectCont)
    throws Exception{
        try{
            String sql = "select " + selectCont + " from room_category order by id";
            return dbm.executeQuery(sql);
        }catch(Exception e){ 
            throw e;
        }
    }
    
    public Map<String, Object> getCategory(int id)
    throws Exception{
        try{
            String sql = "select * from room_category where id = " + id;
            return dbm.executeQuery(sql).get(0);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int getCategoryRoomNum(int category)
    throws Exception{
        try{
            String sql = "select * from " + Room.TABLE_NAME + " where category = " + category + " and is_sub = 0";
            return dbm.executeQuery(sql).size();
        }catch(Exception e){
            throw e;
        }
    }
    
    public String getCategoryJSON(int cid)
    throws Exception{
        try{
            String sql = "select * from " + Room.CATEGORY_TABLE_NAME + " where id = " + cid;
            Map<String, Object> cateRec = dbm.executeQuery(sql).get(0);
            
            return JSONParser.MapToJSON(cateRec);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int addCategory(Map<String, String[]> paraMap)
    throws Exception{
        try{
            Map<String, Object> newParaMap = dbm.packageParaMap(paraMap);
            if(newParaMap.containsKey("wifi") == false){
                newParaMap.put("wifi", 0);
            }
            if(newParaMap.containsKey("bathroom") == false){
                newParaMap.put("bathroom", 0);
            }
            return dbm.insert(Room.CATEGORY_TABLE_NAME, newParaMap);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int editCategory(Map<String, String[]> paraMap)
    throws Exception{
        try{
            Map<String, Object> newParaMap = dbm.packageParaMap(paraMap);
            if(newParaMap.containsKey("wifi") == false){
                newParaMap.put("wifi", 0);
            }
            if(newParaMap.containsKey("bathroom") == false){
                newParaMap.put("bathroom", 0);
            }
            Map<String, Object> whereMap = new HashMap<String, Object>();
            whereMap.put("id", newParaMap.get("id"));
            newParaMap.remove("id");
            
            return dbm.update(Room.CATEGORY_TABLE_NAME, newParaMap, whereMap);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int deleteCategory(int id)
    throws Exception{
        try{
            String sql = "delete from " + Room.CATEGORY_TABLE_NAME + " where id = " + id;
            return dbm.executeUpdate(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public List<Map<String, Object>> getRooms()
    throws Exception{
        try{
            String sql = "select * from " + Room.TABLE_NAME + " order by category";
            return dbm.executeQuery(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public Map<String, Object> getRoomByRoomId(int rid)
    throws Exception{
        try{
            String sql = "select * from " + Room.TABLE_NAME + " where id = " + rid;
            return dbm.executeQuery(sql).get(0);
        }catch(Exception e){
            throw e;
        }
    }
    
    public List<Map<String, Object>> getSubInfo()
    throws Exception{
        try{
            String sql = "select * from " + Room.SUB_TABLE_NAME;
            return dbm.executeQuery(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public Map<String, Object> getSubInfo(int id)
    throws Exception{
        try{
            String sql = "select * from " + Room.SUB_TABLE_NAME + " where id = " + id;
            return dbm.executeQuery(sql).get(0);
        }catch(Exception e){
            throw e;
        }
    }
    
    public List<Map<String, Object>> getSubRooms()
    throws Exception{
        try{
            String sql = "select * from " + Room.TABLE_NAME + " where is_sub = '1'";
            return dbm.executeQuery(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public List<Map<String, Object>> getSubRoomInfoByUserId(int uid)
    throws Exception{
        try{
            String sql = "select * from " + Room.SUB_TABLE_NAME + " where uid = " + uid;
            return dbm.executeQuery(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int subCheckout(int id)
    throws Exception{
        try{
            String roomIdSql = "select rid from " + Room.SUB_TABLE_NAME + " where id = " + id;
            int roomId = (int)dbm.executeQuery(roomIdSql).get(0).get("rid");
            checkoutRoom(roomId);
            String sql = "delete from " + Room.SUB_TABLE_NAME + " where id = " + id;
            return dbm.executeUpdate(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public Map<String, Object> getRoom(int id)
    throws Exception{
        try{
            String sql = "select * from " + Room.TABLE_NAME + " where id = " + id;
            return dbm.executeQuery(sql).get(0);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int addRoom(Map<String, String[]> paraMap)
    throws Exception{
        try{
            Map<String, Object> newParaMap = dbm.packageParaMap(paraMap);
            Calendar cal = Calendar.getInstance();
            String time = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DAY_OF_MONTH);
            System.out.println(time);
            newParaMap.put("create_date", time);
            
            return dbm.insert(Room.TABLE_NAME, newParaMap);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int editRoom(Map<String, String[]> paraMap)
    throws Exception{
        try{
            Map<String, Object> newParaMap = dbm.packageParaMap(paraMap);
            Map<String, Object> whereMap = new HashMap<String, Object>();
            whereMap.put("id", newParaMap.get("old_id"));
            newParaMap.remove("old_id");
            
            return dbm.update("room", newParaMap, whereMap);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int deleteRoom(int id)
    throws Exception{
        try{
            String sql = "delete from room where id = " + id;
            return dbm.executeUpdate(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int subscribe(Map<String, String[]> paraMap)
    throws Exception{
        try{
            Map<String, Object> newParaMap = dbm.packageParaMap(paraMap);
            
            double deposit = Double.parseDouble((String) newParaMap.get("deposit"));
            int bookNum = Integer.parseInt((String)newParaMap.get("book_num"));
            int total_time = Integer.parseInt((String)newParaMap.get("total_time"));
            int cid = Integer.parseInt((String)newParaMap.get("cid"));
            
            newParaMap.put("deposit", deposit * total_time);
            newParaMap.remove("date_range");
            newParaMap.remove("book_num");
            newParaMap.remove("cid");
            
            int count = 0;
            String roomIdSql = "select id from " + Room.TABLE_NAME + " where is_sub = 0 and category = " + cid + " limit " + bookNum;
            int[] roomId = dbm.getIdList(roomIdSql);
            for(int i = 0; i < bookNum; i++){
                newParaMap.put("rid", roomId[i]);
                subRoom(roomId[i]);
                count += dbm.insert(Room.SUB_TABLE_NAME, newParaMap);
            }
            
            String cateGetSql = "select sub_time from " + Room.CATEGORY_TABLE_NAME + " where id = " + cid;
            Map<String, Object> cateMap = dbm.executeQuery(cateGetSql).get(0);
            System.out.println(cateMap.get("sub_time"));
            String cateUpdateSql = "update " + Room.CATEGORY_TABLE_NAME + " set sub_time = " + ((int)cateMap.get("sub_time") + bookNum) + " where id = " + cid;
            dbm.executeUpdate(cateUpdateSql);
            
            return count;
        }catch(Exception e){
            throw e;
        }
    }
    
    public int subRoom(int id)
    throws Exception{
        try{
            String sql = "update " + Room.TABLE_NAME + " set is_sub = 1 where id = " + id;
            return dbm.executeUpdate(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int renewalRoom(int id, int day)
    throws Exception{
        try{
            String sql = "select * from " + Room.SUB_TABLE_NAME + " where id = " + id;
            Map<String, Object> oldSubInfo = dbm.executeQuery(sql).get(0);
            int newTotalTime = (int)oldSubInfo.get("total_time") + day;
            
            sql = "update " + Room.SUB_TABLE_NAME + " set total_time = " + newTotalTime + " where id = " + id;
            return dbm.executeUpdate(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public int checkoutRoom(int id)
    throws Exception{
        try{
            String sql = "update " + Room.TABLE_NAME + " set is_sub = 0 where id = " + id;
            return dbm.executeUpdate(sql);
        }catch(Exception e){
            throw e;
        }
    }
    
    public void close()
    throws Exception{
        try{
            dbm.close();
        }catch(Exception e){
            throw e;
        }
    }
    
}

